The present invention relates to database management systems, and in particular, to controlling access to data managed by a database system.
In a database management system (DBMS), data is stored in one or more data containers, each container contains records, and the data within each record is organized into one or more fields. In relational database systems, the data containers are referred to as tables, the records are referred to as rows, and the fields are referred to as columns. In object oriented databases, the data containers are referred to as object classes, the records are referred to as objects, and the fields are referred to as attributes. Other database architectures may use other terminology.
Systems that implement the present invention are not limited to any particular type of data container or database architecture. However, for the purpose of explanation, the examples and the terminology used herein shall be that typically associated with relational databases. Thus, the terms xe2x80x9ctablexe2x80x9d, xe2x80x9crowxe2x80x9d and xe2x80x9ccolumnxe2x80x9d shall be used herein to refer respectively to the data container, record, and field.
For various reasons, it may not be desirable for all users to have access to all of the rows of a particular table. For example, some rows in a table may contain text in English, while other rows contain text in Spanish. In this case, it would be convenient to limit the access of English-speaking users to the rows containing English, and the access of Spanish-speaking users to the rows containing Spanish.
It may also be desirable to restrict access to certain rows for security reasons. For example, certain rows of a table may contain top secret information, other rows may contain secret information, while other rows contain unclassified information. Under these conditions, the rows made available to any given user should be dictated by the security clearance of that user.
Both of the situations described above require row-level filtering of data, and the second situation also requires that the filtering enforce an access-control policy. To enforce row-level access-control policies, a database server must have a mechanism for restricting users to particular subsets of the rows within tables. One technique for implementing row-level access-control policies involves causing all access to a table to be performed indirectly through xe2x80x9cviewsxe2x80x9d.
A view is a logical table. As logical tables, views may be queried by users as if they were a table. However, views actually present data that is extracted or derived from existing tables. A view is defined by metadata referred to as a view definition. The view definition contains mappings to one or more columns in the one or more tables containing the data. Typically, the view definition is in the form of a database query. Columns and tables that are mapped to a view are referred to herein as base columns and base tables of the view, respectively.
To restrict a user to a particular subset of rows within a table, the user may be granted rights to access a view that extracts from the table only those rows that belong to that particular subset. For example, assume that a table xe2x80x9ctxe2x80x9d has two columns xe2x80x9ctextxe2x80x9d and xe2x80x9clangxe2x80x9d. The xe2x80x9ctextxe2x80x9d column of each row stores textual information, and the xe2x80x9clangxe2x80x9d column of each row stores data indicating the language of the text of that row. Under these conditions, English speaking users may be restricted to accessing table xe2x80x9ctxe2x80x9d through a view defined as follows:
V1:
SELECT * from t
WHERE t.lang=xe2x80x98Englishxe2x80x99
while Spanish speaking users may be restricted to accessing table xe2x80x9ctxe2x80x9d through a view defines as follows:
V2:
SELECT * from t
WHERE t.lang.=xe2x80x98Spanishxe2x80x99
Each of these view definitions contain a WHERE clause that specifies conditions that restrict the rows of table t that are retrieved by the views. These conditions are referred to as the xe2x80x9cpredicatesxe2x80x9d of the view definitions.
Views offer a convenient way to provide row-level access control when the users fall into a relatively small number of categories. For example, if users are categorized solely on the basis of language and only two languages are supported, then only two views need to be created. However, many access policies require users to be divided into a large number of categories based on multiple criteria.
For example, an access policy may require access to be determined based on a user""s language, security clearance, and department. If the database system supports 20 languages, 5 security clearance levels, and 15 departments, the number of views required to implement the access policy would be 20*5*15=1500. For example, the view that would be used by English speaking users with security clearance of 3 that belong to the Insurance department may be defined as follows:
V3:
SELECT * from t
WHERE t.lang=xe2x80x98Englishxe2x80x99
AND t.security_level less than =3
AND t.dept=xe2x80x98Insurancexe2x80x99
View V3 uses xe2x80x9chard-codedxe2x80x9d literals, such as xe2x80x98Englishxe2x80x99, 3, and xe2x80x98Insurancexe2x80x99. The use of hard-coded literals in view definitions can have serious performance implications. Specifically, the use of literals renders similar queries (that would otherwise be able to share query plans and resources) into distinct queries that can share no resources.
In some cases, access policies may dictate that every individual is to be treated differently. For example, an access policy may dictate that every user is only able to access rows that contain his or her user_id. Under these circumstances, a separate view would have to be created for every user. The view for a particular user would, for example, be defined as follows:
V4:
SELECT * from t
WHERE t.user_id=5837392
Under certain conditions, the proliferation of views may be avoided through the use of bind variables. This requires that the database application issue SQL containing bind variables, such as in the following example:
V5:
SELECT * from t
WHERE t.user_id=:BINDVAR
In the query V5, BINDVAR is a bind variable that may be set to a value by the user prior to submitting the query V5. For example, the user that has the user_id of 5837392 can set BINDVAR to 5837392 prior to accessing data using query V5. In this case, query V5 would extract from table t the same rows as the query that defines view V4, in which the user_id value is hard-coded.
Note that the bind variable solution depends entirely on ubiquitous coding in the database application itself. This is not only potentially weaker from a security standpoint, but it is tedious and difficult to code for the application programmers.
Because each user can tailor query V5 by setting BINDVAR to his or her own user_id, only one query is required to implement a policy that would otherwise have required one view per user.
Unfortunately, if the user with the user_id of 5837392 can set BINDVAR to 5837392, then it may be possible for other users to do so as well. When bind variables are used in queries that implement an access policy, the entity that has power to set the bind variables typically also has the power to violate the policy. In many instances, the entity that sets the bind variables is a database application. If the database application is not secure, or if it is possible for users to circumvent the database application, then the data protected by the access policy is not secure.
Another approach to avoid the use of multiple views to implement an access control policy involves the use of a xe2x80x9csessionxe2x80x9d table. When a session is started, a row is added to the session table for the newly started session. The row indicates the session_id of the new session, as well as any other values that are factors used in the access control policy. For example, the session table entry may include the user_id, language, and department of the user that has established the session. All users are then required to access the protected table through a view that performs a join between the session table and the protected table. Such a view may be defined as follows:
V6:
SELECT * from t, session
WHERE t.lang=session.lang
AND t.dept=session.dept
AND session.session_id=CURRENT_SESSION_ID
where CURRENT_SESSION_ID is a value from a session pseudo-column that is set to the session id of the session in which the view is being accessed.
The session table approach has some significant drawbacks. For example, the extra join with the session table can severely affect the efficiency of query execution. In addition, the maintenance of the session table is difficult. For example, simply cleaning up the table to remove sessions that are no longer active can be problematic, especially if a client session were to terminate abnormally and thus not execute any required closing logic.
In addition to the problem of view proliferation, another problem associated with using views to implement an access policy is that not all policy rules can be easily evaluated by the database server. For example, if the access control policy is xe2x80x9ca user accessing the EMP table as a Payroll clerk through the Payroll application is allowed to see all EMP information, including SALARY, but only for employees in her division,xe2x80x9d then views will probably not be able to implement the policy, since the database server will not be able to determine what application a user is using.
Another problem associated with using views to implement an access control policy is that, frequently, users need at least query access to base tables. Users who have privileges on base tables are able to bypass the security enforcement provided by views.
Another problem associated with using views to implement an access control policy is that it can make the access control policy extremely difficult to administer. For example, when a security rule is added, changed, or removed, it may be very difficult to figure out what exactly must be done with each view. An administrator cannot tell whether changing security policies (for example, by altering or dropping a view) will break a database application.
Based on the foregoing, it is clearly desirable to provide a mechanism for implementing access control policies within a database, where the mechanism (1) does not severely impact the efficiency of query execution, (2) does not rely on users to access data through a particular view or set variables to the appropriate values, (3) supports relatively complex access control rules, and (4) does not make access control management impracticably complex.
Provided is a technique for controlling access to data in a database system. According to an aspect of the present invention, groups of security policies are established for a database schema object, such as a table or a view. A security policy reflects access rules for accessing the database schema object. Access to the database schema object is restricted based on security policy groups selected for the user. The security policy groups are selected based on information associated with a user that is maintained or accessed by the database system.
According to another aspect of the present invention, a default security policy is established and used to restrict access of users accessing the database schema object. The information associated with the user contains an attribute that identifies a policy group. The database management system uses the attribute to select policy groups that restrict the user""s access to the database scheme object. When the attribute does not identify any valid security policy group established for the database schema object, all security policies established for the database schema object are used to restrict access to the database schema object.